﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Articles
{
    internal static class ArticleDAL
    {
        internal static ArticleEntity GetArticleByReader(IDataReader dr)
        {
            ArticleEntity article = new ArticleEntity();

            article.ArticleID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            article.CategoryID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            article.Title = dr.IsDBNull(2) ? null : dr.GetString(2);
            article.Author = dr.IsDBNull(3) ? null : dr.GetString(3);
            article.Source = dr.IsDBNull(4) ? null : dr.GetString(4);
            article.Content = dr.IsDBNull(5) ? null : dr.GetString(5);
            article.Tag = dr.IsDBNull(6) ? null : dr.GetString(6);
            article.Editor = dr.IsDBNull(7) ? null : dr.GetString(7);
            article.Hits = dr.IsDBNull(8) ? 0 : dr.GetInt32(8);
            article.Created = Util.ConverDateTime(dr.GetValue(9));
            article.Updated = Util.ConverDateTime(dr.GetValue(10));
            article.Sort = dr.IsDBNull(11) ? 0 : dr.GetInt32(11);
            article.IsEnable = dr.IsDBNull(12) ? false : dr.GetBoolean(12);
            article.CategoryName = dr.IsDBNull(13) ? null : dr.GetString(13);
            article.ProvinceID = dr.IsDBNull(14) ? 0 : dr.GetInt32(14);
            article.CityID = dr.IsDBNull(15) ? 0 : dr.GetInt32(15);
            article.ProvinceName = dr.IsDBNull(16) ? "联盟" : dr.GetString(16);
            article.CityName = dr.IsDBNull(17) ? string.Empty : dr.GetString(17);
            article.ImgUrl = dr.IsDBNull(18) ? "Null.jpg" : dr.GetString(18);

            return article;
        }

        internal static int GetArticleCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectArticle = @"SELECT COUNT([Article].[ArticleID]) 
                FROM Article 
                    INNER JOIN ArticleCategory ON Article.CategoryID = ArticleCategory.CategoryID
                    LEFT OUTER JOIN Area AS CityArea ON Article.CityID = CityArea.AreaID 
                    LEFT OUTER JOIN Area AS ProvinceArea ON Article.ProvinceID = ProvinceArea.AreaID
                ";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectArticle, ref parameterlst);
            DbCommand SelectArticleCommand = db.GetSqlStringCommand(SelectArticle);
            DbHelper.PackDbCommandByDbParameters(SelectArticleCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectArticleCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<ArticleEntity> GetArticleList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<ArticleEntity> articlelst = new List<ArticleEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectArticle = @"
                SELECT [ArticleID],[CategoryID],[Title],[Author],[Source],[Content],[Tag],[Editor],[Hits],[Created],
                    [Updated],[Sort],[IsEnable],[CategoryName],[ProvinceID],[CityID],[ProvinceName],[CityName],[ImgUrl]
                FROM (
                    SELECT Article.ArticleID, Article.CategoryID, Article.Title, Article.Author, Article.Source, Article.[Content], 
                        Article.Tag, Article.Editor, Article.Hits, Article.Created, Article.Updated, Article.Sort, Article.IsEnable,  
                        ArticleCategory.Name AS CategoryName, Article.[ProvinceID], Article.[CityID], ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName,
                        Article.[ImgUrl],ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectArticle += " " + sortField + " " + sortMode + ",";
            SelectArticle += @" Article.[ArticleID] Desc
                    ) AS rowNum 
                        FROM Article 
                            INNER JOIN ArticleCategory ON Article.CategoryID = ArticleCategory.CategoryID 
                            LEFT OUTER JOIN Area AS CityArea ON Article.CityID = CityArea.AreaID 
                            LEFT OUTER JOIN Area AS ProvinceArea ON Article.ProvinceID = ProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectArticle, ref parameterlst);
            SelectArticle += @"
                ) AS [article] 
                WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectArticleCommand = db.GetSqlStringCommand(SelectArticle);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectArticleCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectArticleCommand))
            {
                while (dr.Read())
                {
                    ArticleEntity article = GetArticleByReader(dr);

                    articlelst.Add(article);
                }
            }

            return articlelst;
        }

        internal static ArticleEntity GetArticle(int articleID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            ArticleEntity article = null;

            string SelectArticle = @"
                    SELECT Article.ArticleID, Article.CategoryID, Article.Title, Article.Author, Article.Source, Article.[Content], 
                        Article.Tag, Article.Editor, Article.Hits, Article.Created, Article.Updated, Article.Sort, Article.IsEnable,  
                        ArticleCategory.Name AS CategoryName, Article.[ProvinceID], Article.[CityID], ProvinceArea.Name AS ProvinceName,
                        CityArea.Name AS CityName, Article.[ImgUrl]
                    FROM Article 
                        INNER JOIN ArticleCategory ON Article.CategoryID = ArticleCategory.CategoryID 
                        LEFT OUTER JOIN Area AS CityArea ON Article.CityID = CityArea.AreaID 
                        LEFT OUTER JOIN Area AS ProvinceArea ON Article.ProvinceID = ProvinceArea.AreaID
                    WHERE Article.ArticleID=@ArticleID
                ";

            DbCommand SelectArticleCommand = db.GetSqlStringCommand(SelectArticle);
            db.AddInParameter(SelectArticleCommand, "@ArticleID", DbType.Int32, articleID);

            using (IDataReader dr = db.ExecuteReader(SelectArticleCommand))
            {
                if (dr.Read())
                {
                    article = GetArticleByReader(dr);
                }
            }

            return article;
        }

        internal static int InsertArticle(ArticleEntity article)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertArticle = @"INSERT INTO [Article] 
				([CategoryID],[Title],[Author],[Source],[Content],[Tag],[Editor],[Created],[Updated],[Sort],
                [IsEnable],[Hits],[ProvinceID],[CityID],[ImgUrl])
				 VALUES (@CategoryID,@Title,@Author,@Source,@Content,@Tag,@Editor,@Created,@Updated,@Sort,
                @IsEnable,@Hits,@ProvinceID,@CityID,@ImgUrl)";

            DbCommand InsertArticleCommand = db.GetSqlStringCommand(InsertArticle);
            db.AddInParameter(InsertArticleCommand, "@CategoryID", DbType.Int32, article.CategoryID);
            db.AddInParameter(InsertArticleCommand, "@Title", DbType.String, article.Title);
            db.AddInParameter(InsertArticleCommand, "@Author", DbType.String, article.Author);
            db.AddInParameter(InsertArticleCommand, "@Source", DbType.String, article.Source);
            db.AddInParameter(InsertArticleCommand, "@Content", DbType.String, article.Content);
            db.AddInParameter(InsertArticleCommand, "@Tag", DbType.String, article.Tag);
            db.AddInParameter(InsertArticleCommand, "@Editor", DbType.String, article.Editor);
            db.AddInParameter(InsertArticleCommand, "@Created", DbType.DateTime, article.Created);
            db.AddInParameter(InsertArticleCommand, "@Updated", DbType.DateTime, article.Updated);
            db.AddInParameter(InsertArticleCommand, "@Sort", DbType.Int32, article.Sort);
            db.AddInParameter(InsertArticleCommand, "@IsEnable", DbType.Boolean, article.IsEnable);
            db.AddInParameter(InsertArticleCommand, "@Hits", DbType.Int32, article.Hits);
            db.AddInParameter(InsertArticleCommand, "@ProvinceID", DbType.Int32, article.ProvinceID);
            db.AddInParameter(InsertArticleCommand, "@CityID", DbType.Int32, article.CityID);
            db.AddInParameter(InsertArticleCommand, "@ImgUrl", DbType.String, article.ImgUrl);

            result = db.ExecuteNonQuery(InsertArticleCommand);

            return result;
        }

        internal static int UpdateArticle(ArticleEntity article)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateArticle = @"UPDATE [Article] 
				 SET [CategoryID]=@CategoryID,[Title]=@Title,[Author]=@Author,[Source]=@Source,
                    [Content]=@Content,[Tag]=@Tag,[Editor]=@Editor,[Created]=@Created,[Updated]=@Updated,
                    [Sort]=@Sort,[IsEnable]=@IsEnable,[Hits]=@Hits,[ProvinceID]=@ProvinceID,[CityID]=@CityID,
                    [ImgUrl]=@ImgUrl
				 WHERE [ArticleID] = @ArticleID";

            DbCommand UpdateArticleCommand = db.GetSqlStringCommand(UpdateArticle);
            db.AddInParameter(UpdateArticleCommand, "@ArticleID", DbType.Int32, article.ArticleID);
            db.AddInParameter(UpdateArticleCommand, "@CategoryID", DbType.Int32, article.CategoryID);
            db.AddInParameter(UpdateArticleCommand, "@Title", DbType.String, article.Title);
            db.AddInParameter(UpdateArticleCommand, "@Author", DbType.String, article.Author);
            db.AddInParameter(UpdateArticleCommand, "@Source", DbType.String, article.Source);
            db.AddInParameter(UpdateArticleCommand, "@Content", DbType.String, article.Content);
            db.AddInParameter(UpdateArticleCommand, "@Tag", DbType.String, article.Tag);
            db.AddInParameter(UpdateArticleCommand, "@Editor", DbType.String, article.Editor);
            db.AddInParameter(UpdateArticleCommand, "@Created", DbType.DateTime, article.Created);
            db.AddInParameter(UpdateArticleCommand, "@Updated", DbType.DateTime, article.Updated);
            db.AddInParameter(UpdateArticleCommand, "@Sort", DbType.Int32, article.Sort);
            db.AddInParameter(UpdateArticleCommand, "@IsEnable", DbType.Boolean, article.IsEnable);
            db.AddInParameter(UpdateArticleCommand, "@Hits", DbType.Int32, article.Hits);
            db.AddInParameter(UpdateArticleCommand, "@ProvinceID", DbType.Int32, article.ProvinceID);
            db.AddInParameter(UpdateArticleCommand, "@CityID", DbType.Int32, article.CityID);
            db.AddInParameter(UpdateArticleCommand, "@ImgUrl", DbType.String, article.ImgUrl);

            result = db.ExecuteNonQuery(UpdateArticleCommand);

            return result;
        }

        internal static int DeleteArticle(int articleID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteArticle = @"
                DELETE FROM [Article] WHERE ArticleID=@ArticleID";

            DbCommand DeleteArticleCommand = db.GetSqlStringCommand(DeleteArticle);
            db.AddInParameter(DeleteArticleCommand, "@ArticleID", DbType.Int32, articleID);
            result = db.ExecuteNonQuery(DeleteArticleCommand);

            return result;
        }

        internal static int SetArticleEnable(int articleID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetArticle = @"UPDATE [Article]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [ArticleID] = @ArticleID";

            DbCommand SetArticleCommand = db.GetSqlStringCommand(SetArticle);
            db.AddInParameter(SetArticleCommand, "@ArticleID", DbType.Int32, articleID);
            result = db.ExecuteNonQuery(SetArticleCommand);

            return result;
        }

        internal static int HitArticle(int articleID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetArticle = @"UPDATE [Article]
                    SET [Hits] = [Hits]+1
                    WHERE [ArticleID] = @ArticleID";

            DbCommand SetArticleCommand = db.GetSqlStringCommand(SetArticle);
            db.AddInParameter(SetArticleCommand, "@ArticleID", DbType.Int32, articleID);
            result = db.ExecuteNonQuery(SetArticleCommand);

            return result;
        }

        internal static int GetSearchCount(SearchUrlEntity searchUrl)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            StringBuilder SelectArticle = new StringBuilder();
            SelectArticle.Append("SELECT COUNT([ArticleID]) FROM [Article] ");
            string[] containAllKeywordlst = ArticleHelper.GetItems(searchUrl.ContainAllKeyword);
            string[] containAnyKeywordlst = ArticleHelper.GetItems(searchUrl.ContainAnyKeyword);
            string[] locationlst = ArticleHelper.GetItems(searchUrl.Location);
            StringBuilder wherecontains = new StringBuilder();
            #region Contains
            foreach (string location in locationlst)
            {
                if (string.IsNullOrEmpty(wherecontains.ToString()))
                {
                    wherecontains.Append("(CONTAINS(").Append(location).Append(",'");
                }
                else
                {
                    wherecontains.Append(" OR CONTAINS(").Append(location).Append(",'");
                }
                for (int n = 0; n < containAllKeywordlst.Length; n++)
                {
                    if (n == 0)
                    {
                        wherecontains.Append("\"").Append(containAllKeywordlst[n]).Append("\"");
                    }
                    else
                    {
                        wherecontains.Append(" AND \"").Append(containAllKeywordlst[n]).Append("\"");
                    }
                }
                for (int n = 0; n < containAnyKeywordlst.Length; n++)
                {
                    wherecontains.Append(" OR \"").Append(containAnyKeywordlst[n]).Append("\"");
                }
                wherecontains.Append("') ");
            }
            if (!string.IsNullOrEmpty(wherecontains.ToString()))
            {
                wherecontains.Append(") ");
            }
            #endregion 
            #region Category
            if (searchUrl.Category != 0)
            {
                if (string.IsNullOrEmpty(wherecontains.ToString()))
                {
                    wherecontains.Append(" CategoryID=").Append(searchUrl.Category);
                }
                else
                {
                    wherecontains.Append(" AND CategoryID=").Append(searchUrl.Category);
                }
            }
            #endregion
            #region BegDate EndDate
            if (!string.IsNullOrEmpty(searchUrl.BegDate) || !string.IsNullOrEmpty(searchUrl.EndDate))
            {
                wherecontains.Append(" AND (");
                if (!string.IsNullOrEmpty(searchUrl.BegDate) && !string.IsNullOrEmpty(searchUrl.EndDate))
                {
                    wherecontains.Append("Created>='").Append(searchUrl.BegDate).Append(" 00:00:00.000'")
                        .Append(" AND Created<='").Append(searchUrl.EndDate).Append(" 23:59:59.997'");
                }
                else
                {
                    if (!string.IsNullOrEmpty(searchUrl.BegDate))
                    {
                        wherecontains.Append("Created>='").Append(searchUrl.BegDate).Append(" 00:00:00.000'");
                    }
                    if (!string.IsNullOrEmpty(searchUrl.EndDate))
                    {
                        wherecontains.Append("Created<='").Append(searchUrl.EndDate).Append(" 23:59:59.997'");
                    }
                }
                wherecontains.Append(") ");
            }
            #endregion
            if (!string.IsNullOrEmpty(wherecontains.ToString()))
            {
                SelectArticle.Append("WHERE ").Append(wherecontains.ToString());
            }

            DbCommand SelectArticleCommand = db.GetSqlStringCommand(SelectArticle.ToString());

            object obj = db.ExecuteScalar(SelectArticleCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<ArticleEntity> GetSearchList(SearchUrlEntity searchUrl)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<ArticleEntity> articlelst = new List<ArticleEntity>();

            StringBuilder SelectArticle = new StringBuilder();
            SelectArticle.Append(@"
                SELECT [ArticleID],[CategoryID],[Title],[Author],[Source],[Content],[Tag],[Editor],[Created],
                    [Updated],[Sort],[IsEnable],[Hits]
                FROM (
                    SELECT [ArticleID],[CategoryID],[Title],[Author],[Source],[Content],[Tag],[Editor],[Created],
                        [Updated],[Sort],[IsEnable],[Hits],ROW_NUMBER() OVER (ORDER BY [Created] Desc) AS rowNum 
                    FROM Article ");
            string[] containAllKeywordlst = ArticleHelper.GetItems(searchUrl.ContainAllKeyword);
            string[] containAnyKeywordlst = ArticleHelper.GetItems(searchUrl.ContainAnyKeyword);
            string[] locationlst = ArticleHelper.GetItems(searchUrl.Location);
            StringBuilder wherecontains = new StringBuilder();
            #region Contains
            foreach (string location in locationlst)
            {
                if (string.IsNullOrEmpty(wherecontains.ToString()))
                {
                    wherecontains.Append("(CONTAINS(").Append(location).Append(",'");
                }
                else
                {
                    wherecontains.Append(" OR CONTAINS(").Append(location).Append(",'");
                }
                for (int n = 0; n < containAllKeywordlst.Length; n++)
                {
                    if (n == 0)
                    {
                        wherecontains.Append("\"").Append(containAllKeywordlst[n]).Append("\"");
                    }
                    else
                    {
                        wherecontains.Append(" AND \"").Append(containAllKeywordlst[n]).Append("\"");
                    }
                }
                for (int n = 0; n < containAnyKeywordlst.Length; n++)
                {
                    wherecontains.Append(" OR \"").Append(containAnyKeywordlst[n]).Append("\"");
                }
                wherecontains.Append("') ");
            }
            if (!string.IsNullOrEmpty(wherecontains.ToString()))
            {
                wherecontains.Append(") ");
            }
            #endregion 
            #region Category
            if (searchUrl.Category != 0)
            {
                if (string.IsNullOrEmpty(wherecontains.ToString()))
                {
                    wherecontains.Append(" CategoryID=").Append(searchUrl.Category);
                }
                else
                {
                    wherecontains.Append(" AND CategoryID=").Append(searchUrl.Category);
                }
            }
            #endregion
            #region BegDate EndDate
            if (!string.IsNullOrEmpty(searchUrl.BegDate) || !string.IsNullOrEmpty(searchUrl.EndDate))
            {
                wherecontains.Append(" AND (");
                if (!string.IsNullOrEmpty(searchUrl.BegDate) && !string.IsNullOrEmpty(searchUrl.EndDate))
                {
                    wherecontains.Append("Created>='").Append(searchUrl.BegDate).Append(" 00:00:00.000'")
                        .Append(" AND Created<='").Append(searchUrl.EndDate).Append(" 23:59:59.997'");
                }
                else
                {
                    if (!string.IsNullOrEmpty(searchUrl.BegDate))
                    {
                        wherecontains.Append("Created>='").Append(searchUrl.BegDate).Append(" 00:00:00.000'");
                    }
                    if (!string.IsNullOrEmpty(searchUrl.EndDate))
                    {
                        wherecontains.Append("Created<='").Append(searchUrl.EndDate).Append(" 23:59:59.997'");
                    }
                }
                wherecontains.Append(") ");
            }
            #endregion
            if (!string.IsNullOrEmpty(wherecontains.ToString()))
            {
                SelectArticle.Append("WHERE ").Append(wherecontains.ToString());
            }
            SelectArticle.Append(") AS [Article] ");
            SelectArticle.Append("WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ");

            DbCommand SelectArticleCommand = db.GetSqlStringCommand(SelectArticle.ToString());
            db.AddInParameter(SelectArticleCommand, "@StartRecordIndex", DbType.Int32, (searchUrl.PageIndex - 1) * searchUrl.PageSize + 1);
            db.AddInParameter(SelectArticleCommand, "@EndRecordIndex", DbType.Int32, searchUrl.PageIndex * searchUrl.PageSize);

            using (IDataReader dr = db.ExecuteReader(SelectArticleCommand))
            {
                while (dr.Read())
                {
                    articlelst.Add(GetArticleByReader(dr));
                }
            }

            return articlelst;
        }
    }
}
